from collections import OrderedDict
from sqlalchemy.ext.declarative import DeclarativeMeta
import json


# 自定义json显示(方法一)
def cls_to_dict(cls):
    fields = cls.__dict__
    dict = {}
    for (k, v) in fields.items():
        if (k == "_sa_instance_state"):
            continue
        dict[k] = v
        list_json = OrderedDict(
            dict
        )
    return list_json


# 自定义json显示(方法二)
class AlchemyEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj.__class__, DeclarativeMeta):
            # an SQLAlchemy class
            fields = {}
            for field in [x for x in dir(obj) if not x.startswith('_') and x != 'metadata']:
                data = obj.__getattribute__(field)
                try:
                    json.dumps(data)  # this will fail on non-encodable values, like other classes
                    fields[field] = data
                except TypeError:
                    fields[field] = None
            # a json-encodable dict
            return fields

        return json.JSONEncoder.default(self, obj)


def importSqlFile(file, session):
    # Open the .sql file
    sql_file = open(file, 'r',encoding="utf8")

    # Create an empty command string
    sql_command = ''

    # Iterate over all lines in the sql file
    for line in sql_file:
        # Ignore comented lines
        if not line.startswith('--') and line.strip('\n'):
            # Append line to the command string
            sql_command += line.strip('\n')

            # If the command string ends with ';', it is a full statement
            if sql_command.endswith(';'):
                # Try to execute statemente and commit it
                try:
                    session.execute(sql_command)
                    session.commit()

                # Assert in case of error
                except Exception as e:
                    print(e)

                # Finally, clear command string
                finally:
                    sql_command = ''
